package integrationTier.sql;

import core.MovieException;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import core.Genre;
import core.Medium;
import core.Movie;
import integrationTier.IMovieDAO;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author vojta
 */
public class MovieSQLDAO implements IMovieDAO {

    Statement stmt;
    Connection cn;

    public MovieSQLDAO(Connection cn) throws MovieException {
        try {
            this.cn = cn;
            stmt = this.cn.createStatement();
        } catch (SQLException ex) {
            throw new MovieException(ex);
        }
    }

    /**
     * Vypise vsechny filmy
     * @return ArrayList
     * @throws MovieException
     */
    public List<Movie> listAll() throws MovieException {
        try {
            List<Movie> list = new ArrayList<Movie>();
            String q = "SELECT * FROM \"movies\"";
            ResultSet rs = stmt.executeQuery(q);

            while (rs.next()) {
                MediumSQLDAO mDAO = new MediumSQLDAO(this.cn);
                Medium medium = new Medium(rs.getInt("medium_id"), "");
                medium = mDAO.get(medium);


                GenreSQLDAO gDAO = new GenreSQLDAO(this.cn);
                Genre genre = new Genre(rs.getInt("genre_id"), "");
                genre = gDAO.get(genre);


                Movie movie = new Movie(rs.getInt("id"), rs.getString("name"), rs.getInt("year"), rs.getString("director"), medium, genre);

                list.add(movie);
            }

            return list;
        } catch (SQLException ex) {
            throw new MovieException(ex);
        }
    }

    /**
     * Ziska informace o filmu
     * @param movie
     * @return Movie
     * @throws MovieException
     */
    public Movie get(Movie movie) throws MovieException {
        try {
            String q = "SELECT * FROM \"movies\" WHERE \"id\" = " + movie.getId() + "";
            ResultSet rs = stmt.executeQuery(q);
            rs.next();

            MediumSQLDAO mDAO = new MediumSQLDAO(this.cn);
            Medium medium = new Medium(rs.getInt("medium_id"), "");
            medium = mDAO.get(medium);

            GenreSQLDAO gDAO = new GenreSQLDAO(this.cn);
            Genre genre = new Genre(rs.getInt("genre_id"), "");
            genre = gDAO.get(genre);

            movie = new Movie(rs.getInt("id"), rs.getString("name"), rs.getInt("year"), rs.getString("director"), medium, genre);
            return movie;

        } catch (SQLException ex) {
            throw new MovieException(ex);
        }
    }

    /**
     * Prida novy film
     * @param movie
     * @throws MovieException
     */
    public void add(Movie movie) throws MovieException {
        try {
            Medium medium = movie.getMedium();
            Genre genre = movie.getGenre();
            String q = "INSERT INTO \"movies\"(\"name\",\"director\",\"year\",\"medium_id\",\"genre_id\") VALUES('" + movie.getName() + "','" + movie.getDirector() + "'," + movie.getYear() + "," + medium.getId() + "," + genre.getId() + ")";
            stmt.executeUpdate(q);
        } catch (SQLException ex) {
            throw new MovieException(ex);
        }
    }

    /**
     * Odstrani film
     * @param movie
     * @throws MovieException
     */
    public void remove(Movie movie) throws MovieException {
        try {
            String q = "DELETE FROM \"movies\" WHERE \"id\" = " + movie.getId() + "";
            stmt.executeUpdate(q);
        } catch (SQLException ex) {
            throw new MovieException(ex);
        }
    }

    /**
     * Zmeni vybrany film
     * @param movie
     * @throws MovieException
     */
    public void change(Movie movie) throws MovieException {
        try {
            String q = "UPDATE \"movies\" SET \"name\" = '" + movie.getName() + "', \"director\" = '" + movie.getDirector() + "', \"year\" = " + movie.getYear() + ", \"medium_id\" = " + movie.getMedium().getId() + ", \"genre_id\" = " + movie.getGenre().getId() + " WHERE \"id\" = "+ movie.getId() +"";
            stmt.executeUpdate(q);
        } catch (SQLException ex) {
            throw new MovieException(ex);
        }
    }

    public List<Movie> find(Movie movie) throws MovieException {
        List<Movie> list = new ArrayList<Movie>();
        try {
            String q = "SELECT * FROM \"movies\" WHERE ";
            if (!movie.getName().equals("")) {
                q += "LOWER(\"name\") LIKE LOWER('%" + movie.getName() + "%') AND ";
            }
            if (!movie.getDirector().equals("")) {
                q += "LOWER(\"director\") LIKE LOWER('%" + movie.getDirector() + "%') AND ";
            }
            if (movie.getYear() != 0) {
                q += "\"year\" = " + movie.getYear() + " AND ";
            }
            if (movie.getGenre() != null) {
                q += "\"genre_id\" = " + movie.getGenre().getId() + " AND ";
            }
            if (movie.getMedium() != null) {
                q += "\"medium_id\" = " + movie.getMedium().getId() + " AND ";
            }
            q += "1=1";
            
            System.out.println(q);
            
            ResultSet rs = stmt.executeQuery(q);
            
            while (rs.next()) {
                MediumSQLDAO mDAO = new MediumSQLDAO(this.cn);
                Medium medium = new Medium(rs.getInt("medium_id"), "");
                medium = mDAO.get(medium);
                GenreSQLDAO gDAO = new GenreSQLDAO(this.cn);
                Genre genre = new Genre(rs.getInt("genre_id"), "");
                genre = gDAO.get(genre);
                Movie m = new Movie(rs.getInt("id"), rs.getString("name"), rs.getInt("year"), rs.getString("director"), medium, genre);
                
                list.add(m);
            }            
        } catch (SQLException ex) {
            Logger.getLogger(MovieSQLDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return list;
    }

    /**
     * Uzavre spojeni s DB
     * @throws MovieException
     */
    public void close() throws MovieException {
        try {
            stmt.execute("SHUTDOWN");
            stmt.close();
        } catch (SQLException ex) {
            throw new MovieException(ex);
        }

    }
}
